package stujdbc;

import java.rmi.*;
import java.rmi.server.*;
import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DatabaseImpl extends UnicastRemoteObject implements Database {

    Connection connection = null;
    Statement statement = null;
    //
    // DONE:
    //
    ArrayList<Student> studentsal = new ArrayList<Student>();

    public DatabaseImpl() throws RemoteException, ClassNotFoundException {
        // load the sqlite-JDBC driver using the current class loader
        Class.forName("org.sqlite.JDBC");

        try {
            // create a database connection
            connection = DriverManager.getConnection("jdbc:sqlite:stujdbc/students.db");
            statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            statement.executeUpdate("create table if not exists students ("
                + "id integer primary key, name string,"
                + " graduate string, department string, "
                + "email string, phone text)");
        }
        catch (SQLException e) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(e.getMessage());
        }
        finally {
            dbExit();
        }
    }

    @Override
    public Student create() throws RemoteException {
        return new StudentImpl();
    }

    @Override
    public void register(Student student) throws RemoteException {
        try {
            Student s = search(student.getId());
            String sql = null;
            if (s == null) {
                sql = "insert into students values("
                    + student.getId() + ", "
                    + "'" + student.getName() + "', "
                    + "'" + student.getGraduate() + "', "
                    + "'" + student.getDepartment() + "', "
                    + "'" + student.getEmail() + "', "
                    + "'" + student.getPhone() + "'"
                    + ")";

                statement.executeUpdate(sql);

                System.out.println("Register: student " + student.getId());
            }
            else {
                sql = "update students set "
                    + "name = '" + student.getName() + "', "
                    + "graduate = '" + student.getGraduate() + "', "
                    + "department = '" + student.getDepartment() + "', "
                    + "email = '" + student.getEmail() + "', "
                    + "phone = '" + student.getPhone() + "' "
                    + "where id = " + student.getId();
                
                statement.executeUpdate(sql);

                System.out.println("Replace: student " + student.getId());
            }
        }
        catch (SQLException ex) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(ex.getMessage());
        }
    }

    @Override
    public Student search(int id) throws RemoteException {
        Student student = null;
        try {
            String sql = "select * from students where id = " + id;
            ResultSet rs = statement.executeQuery(sql);

            if (rs.next()) {
                student = new StudentImpl();
                student.setId(id);
                student.setName(rs.getString("name"));
                student.setGraduate(rs.getString("graduate"));
                student.setDepartment(rs.getString("department"));
                student.setEmail(rs.getString("email"));
                student.setPhone(rs.getString("phone"));
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(DatabaseImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return student;
    }

    private void dbExit() {
        Runtime.getRuntime().addShutdownHook(new Thread() {

            @Override
            public void run() {
                System.out.println("Exit...");
                try {
                    if (connection != null) {
                        connection.close();
                    }
                }
                catch (SQLException e) {
                    // connection close failed.
                    System.err.println(e);
                }
            }
        });
    }
}
